﻿

CREATE proc [Projects].[GetBusinessCasesShortList]
@CreatedFrom		datetime2(7)	= null,
@CreatedTo			datetime2(7)	= null,
@CustomerGroup		int				= null,
@CustomerSource		int				= null,
@FileUser			int				= null,
@Intern				bit				= null,
@ProjectStrasse		nvarchar(50)	= null,
@EPLZ				int				= null,
@ProjectStatus		tinyint			= null,
@Purchaser			int				= null

as
begin
	set nocount on
	--IF OBJECT_ID (N'#tmpBCShortList') IS NOT NULL
	--begin
	--	drop table #tmpBCShortList	
	--end

	--create table #tmpBCShortList (
	--	ProjectID			int,
	--	CreatedBy			nvarchar(50),
	--	CreatedOn			datetime2(7),
	--	CustomerGroup		tinyint,
	--	CustomerSource		nvarchar(100),
	--	FileUser			nvarchar(50),
	--	FinishDate			datetime2(7),
	--	FirstContactDate	datetime2(7),
	--	ImplementationDate	datetime2(7),
	--	Intern				bit,
	--	Purchaser			nvarchar(50),
	--	InvoiceRecipient	nvarchar(50),
	--	LastChangedBy		nvarchar(50),
	--	LastChangedOn		datetime2(7),
	--	OrderDate			datetime2(7),
	--	ProjectAddress		nvarchar(100),
	--	ProjectStatus		int,
	--	ProjectStatusText	nvarchar(50),
	--	ShortName			nvarchar(50),
	--	ErloesSumNetto		decimal(11,2),
	--	CostSummary			decimal(11,2),
	--	ExtraCostSummary	decimal(11,2),
	--	ResultDB			decimal(11,2),
	--	TimeSummary			decimal(11,2),
	--)

	--;with selBC
	--as
	--(
	--	select	p.ProjectID, 
	--			createUser.LoginName	'CreatedBy',
	--			p.CreateOn,
	--			p.CustomerGroup,
	--			rtrim(cs.CustomerSourceShort)	'CustomerSource',
	--			rtrim(fileUser.LoginName)		'FileUser',
	--			p.FinishDate,
	--			p.FirstContactDate,
	--			p.ImplementationDate,
	--			p.Intern, 
	--			case when pur.Prefix != '' 
	--				then pur.Prefix + ' '
	--				else '' 
	--			end
	--			+ pur.[Name]			'Purchaser',
	--			case when invRec.Prefix != '' 
	--				then invRec.Prefix + ' '
	--				else '' 
	--			end
	--			+ invREc.[Name]			'InvoiceRecipient',
	--			lastChangeUser.LoginName	'LastChangedBy',
	--			p.LastChangedOn,
	--			p.OrderDate,
	--			coalesce(plz.PLZ + ' ','') + coalesce(plz.Ort,'') + coalesce(', ' + p.[Address],'')	'ProjectAddress',
	--			p.ProjectStatus			'ProjectStatus',
	--			ps.StateText			'ProjectStatusText',
	--			p.ShortName, 
	--			pv.ErloesSumNetto,
	--			pv.CostSummary,
	--			pv.ExtraCostSummary,
	--			pv.ResultDB,
	--			pv.TimeSummary
			
	--	from	Projects.datProjects p
	--			inner join Projects.datProjectsValues pv on
	--				pv.ProjectID = p.ProjectID
	--			inner join [User].datUser createUser on
	--				createUser.UserID = p.CreateBy
	--			inner join [dbo].[domCustomerSource] cs on
	--				cs.CustomerSourceID = p.CustomerSource
	--			inner join [User].datUser fileUser on
	--				fileUser.UserID = p.FileUserID
	--			inner join dbo.Organisation invRec on
	--				invRec.OrgKey = p.InvoiceToID
	--			inner join [User].datUser lastChangeUser on
	--				lastChangeUser.UserID = p.LastChangedBy
	--			inner join dbo.Postleitzahl plz on
	--				plz.EPLZ = p.EPLZ
	--			inner join Projects.domProjectStatus ps on
	--				ps.ProjectStatus = p.ProjectStatus
	--			inner join dbo.Organisation pur on
	--				pur.OrgKey = p.PurchaserID
	--	where	(p.CreateOn >= @CreatedFrom or @CreatedFrom is null)
	--			and (p.CreateOn < DATEADD(DAY, 1, @CreatedTo) or @CreatedTo is null)
	--			and (p.Intern = @Intern or @Intern is null)
	--			and (p.EPLZ = @EPLZ or @EPLZ is null)
	--			and (p.PurchaserID = @Purchaser or @Purchaser is null)
	--			and (p.FileUserID = @FileUser or @FileUser is null)
	--			and (p.[Address] like @ProjectStrasse + '%' or @ProjectStrasse is null)
	--			and (p.CustomerSource = @CustomerSource or @CustomerSource is null)
	--)
	--insert into #tmpBCShortList (
	--	ProjectID,
	--	CreatedBy,
	--	CreatedOn,
	--	CustomerGroup,
	--	CustomerSource,
	--	FileUser,
	--	FinishDate,
	--	FirstContactDate,
	--	ImplementationDate,
	--	Intern,
	--	Purchaser,
	--	InvoiceRecipient,
	--	LastChangedBy,
	--	LastChangedOn,
	--	OrderDate,
	--	ProjectAddress,
	--	ProjectStatus,
	--	ProjectStatusText,
	--	ShortName,
	--	ErloesSumNetto,
	--	CostSummary,
	--	ExtraCostSummary,
	--	ResultDB,
	--	TimeSummary
	--)
	--select	bc.ProjectID,
	--		bc.CreatedBy,
	--		bc.CreateOn,
	--		bc.CustomerGroup,
	--		bc.CustomerSource,
	--		bc.FileUser,
	--		bc.FinishDate,
	--		bc.FirstContactDate,
	--		bc.ImplementationDate,
	--		bc.Intern,
	--		bc.Purchaser,
	--		bc.InvoiceRecipient,
	--		bc.LastChangedBy,
	--		bc.LastChangedOn,
	--		bc.OrderDate,
	--		bc.ProjectAddress,
	--		bc.ProjectStatus,
	--		bc.ProjectStatusText,
	--		bc.ShortName,
	--		bc.ErloesSumNetto,
	--		bc.CostSummary,
	--		bc.ExtraCostSummary,
	--		bc.ResultDB,
	--		bc.TimeSummary
	--from	selBC bc


	--if @CustomerGroup is not null
	--begin
	--	delete	#tmpBCShortList
	--	where	CustomerGroup != @CustomerGroup
	--end

	--if @ProjectStatus is not null
	--begin
	--	delete	#tmpBCShortList
	--	where	ProjectStatus != @ProjectStatus
	--end


	--select	sl.ProjectID,
	--		sl.CreatedBy,
	--		sl.CreatedOn,
	--		sl.CustomerGroup,
	--		sl.CustomerSource,
	--		sl.FileUser,
	--		sl.FinishDate,
	--		sl.FirstContactDate,
	--		sl.ImplementationDate,
	--		sl.Intern,
	--		sl.Purchaser,
	--		sl.InvoiceRecipient,
	--		sl.LastChangedBy,
	--		sl.LastChangedOn,
	--		sl.OrderDate,
	--		sl.ProjectAddress,
	--		sl.ProjectStatus,
	--		sl.ProjectStatusText,
	--		sl.ShortName,
	--		sl.ErloesSumNetto,
	--		sl.CostSummary,
	--		sl.ExtraCostSummary,
	--		sl.ResultDB,
	--		sl.TimeSummary
	--from	#tmpBCShortList sl
	--where	sl.FileUser = @FileUser or @FileUser is null
	
end



